// use crate::model::Fund;
use anyhow::anyhow;
use anyhow::Ok;
use anyhow::Result;
use chrono::Local;
use log::trace;
use surrealdb::opt::PatchOp;
use surrealdb::sql::Id;
use surrealdb::sql::Thing;

use chrono::Utc;
use crate::db::FUHLC;
use crate::db::FUDB;
use crate::model::trade::trade_storder::TradeStorder;
use crate::model::FudaRecord;
// use crate::model::trade_storder::OrderMethod;

///
/// force to create a trade_storder record, if any exist will be deleted
///
/// @param trade_storder: trade_storder information
///
pub async fn upsert(trade_storder: &TradeStorder) -> Result<Option<FudaRecord>> {
    let mut old_trade_storder: Option<TradeStorder> = None;
    if let Some(id) = trade_storder.id.as_ref() {
        println!("\n(trade_storder::upsert)trade_storder.id.id.to_raw(): {}", id.id.to_raw());
        old_trade_storder = FUDB.select(("trade_storder", id.id.to_raw())).await?;
    }

    let thing = match &old_trade_storder {
        Some(TradeStorder { id: Some(t), .. }) => t.clone(),
        _ => {
            // Directly creating a new Thing with a ULID
            Thing::from(("trade_storder", Id::ulid()))
        }
    };

    let mut updated: Option<FudaRecord> = None;
    let mut to_update = false;
    let timestamp = FUHLC.new_timestamp().to_string();
    // Update a record with a specific ID
    let mut patch = FUDB.update(thing).patch(PatchOp::replace("/last_updated", timestamp));

    match old_trade_storder {
        Some(TradeStorder { id: Some(t), .. }) => {
            updated = Some(FudaRecord { id: t.into() });
        }
        _ => {
            // 新建，下面这些字段只有在新建时候才会有
            patch = patch.patch(PatchOp::replace("/kline_id", &trade_storder.kline_id));
            patch = patch.patch(PatchOp::replace("/security_id", &trade_storder.security_id));
            patch = patch.patch(PatchOp::replace("/klts", &trade_storder.klts));
            patch = patch.patch(PatchOp::replace("/data", &trade_storder.data));
            to_update = true;
        }
    }

    if to_update {
        updated = patch.await.map_err(|err| anyhow!(err))?;
    }

    Ok(updated)
}

pub async fn upsert_all_trade_storders(torders: &Vec<TradeStorder>) -> Result<()> {
    let start_time = Utc::now();
    // let last_updated = format!("{}", FUHLC.new_timestamp());
    let query = FUDB.query(
        r#"
        FOR $v IN $torders { 
            INSERT INTO trade_storder (id,security_id,kline_id,klts,ohlcv,rank,lowest_lows,mean,variance,std_deviation,abs_deviation,pct_deviation) 
            VALUES ($v.id, $v.security_id, $v.kline_id, $v.klts, $v.ohlcv, $v.rank, $v.lowest_lows, $v.mean, $v.variance, $v.std_deviation, $v.abs_deviation, $v.pct_deviation)
            ON DUPLICATE KEY UPDATE
                security_id=$v.security_id, 
                kline_id=$v.kline_id, 
                klts=$v.klts, 
                ohlcv=$v.ohlcv, 
                rank=$v.rank, 
                lowest_lows=$v.lowest_lows, 
                mean=$v.mean, 
                variance=$v.variance, 
                std_deviation=$v.std_deviation, 
                abs_deviation=$v.abs_deviation, 
                pct_deviation=$v.pct_deviation;
        }
        "#
    ).bind(("torders", torders));

    let _rs = query.await?;

    let spent_time = Local::now().signed_duration_since(start_time);
    trace!(
        "upsert_all_deviations  spent seconds={}.{} ",
        spent_time.num_seconds(),
        spent_time.num_milliseconds() % 1000
    );

    Ok(())
}

///
/// delete trade_storder by it's id , e.g. SH.STK.600519
///
pub async fn delete(trade_storder_id: &str) -> Result<TradeStorder> {
    let rs: Option<TradeStorder> = FUDB.delete(("trade_storder", trade_storder_id)).await?;
    match rs {
        Some(trade_storder) => Ok(trade_storder),
        None =>
            Err(
                anyhow!(format!("Failed to delete, TradeStbuy not found for {}", trade_storder_id))
            ),
    }
}

///
/// delete one trade_storder by it's trade_bot_id+kline_id
///
/// as trade_bot_id+kline_id decide unique trade_storder
///
pub async fn delete_one_by(
    trade_bot_id: &str,
    kline_id: &str
) -> Result<Vec<Option<TradeStorder>>> {
    let q_str = format!(
        "DELETE trade_storder WHERE trade_bot_id={} AND kline_id={}",
        trade_bot_id,
        kline_id
    );

    // Fetch kline data within the specified date range using bind variables
    let query = FUDB.query(q_str);

    let mut response = query.await?;

    println!("\n(trade_storder::delete_one_by) response= {:?}\n", response);

    let values: Vec<Option<TradeStorder>> = response.take(0)?;

    println!("\n(trade_storder::delete_one_by) values= {:?}\n", values);

    Ok(values)
}
///
/// delete all trade_storder by it's trade_bot_id
///
pub async fn delete_all_by(trade_bot_id: &str) -> Result<Vec<Option<TradeStorder>>> {
    let q_str = format!("DELETE FROM trade_storder WHERE trade_bot_id='{}'", trade_bot_id);

    let query = FUDB.query(&q_str);

    let mut response = query.await?;

    println!("\n(trade_storder::delete_all_by) q_str= {q_str} response= {:?}\n", response);

    let values: Vec<Option<TradeStorder>> = response.take(0)?;

    println!("\n(trade_storder::delete_all_by) values= {:?}\n", values);

    Ok(values)
}

/// select trade_storders
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select(
    trade_bot_id: Option<String>,
    start_kline_id: Option<String>,
    end_kline_id: Option<String>,
    pi: u16,
    pn: u16
) -> Result<Vec<TradeStorder>> {
    let q_str_part = match (&trade_bot_id, &start_kline_id, &end_kline_id) {
        (None, None, None) => "",
        (None, None, Some(_)) => "WHERE kline_id <= $end_kline_id",
        (None, Some(_), None) => "WHERE kline_id >= $start_kline_id ",
        (None, Some(_), Some(_)) =>
            "WHERE kline_id >= $start_kline_id AND kline_id <= $end_kline_id",
        (Some(_), None, None) => "WHERE trade_bot_id = $trade_bot_id",
        (Some(_), None, Some(_)) =>
            "WHERE trade_bot_id = $trade_bot_id AND kline_id <= $end_kline_id",
        (Some(_), Some(_), None) =>
            "WHERE trade_bot_id = $trade_bot_id AND kline_id >= $start_kline_id",
        (Some(_), Some(_), Some(_)) => {
            "WHERE trade_bot_id = $trade_bot_id AND kline_id >= $start_kline_id AND kline_id <= $end_kline_id"
        }
    };

    let start = pi * pn;

    let q_str = &format!(
        "SELECT * FROM trade_storder {} ORDER BY klts DESC LIMIT {} START {}",
        q_str_part,
        pn,
        start
    );

    // Fetch kline data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    if let Some(t_) = &trade_bot_id {
        query = query.bind(("trade_bot_id", t_)); // Bind the start_date variable
    }

    if let Some(t_) = &start_kline_id {
        query = query.bind(("start_kline_id", t_)); // Bind the start_date variable
    }

    if let Some(t_) = &end_kline_id {
        query = query.bind(("end_kline_id", t_)); // Bind the start_date variable
    }

    // println!("\nq_str={} \n\nquery={:?}", q_str, query);

    let mut response = query.await?;

    // println!("\n(trade_storder::select) response= {:?}\n", response);

    let values: Vec<TradeStorder> = response.take(0)?;

    Ok(values)
}

#[cfg(test)]
mod test {
    use chrono::Duration;
    use chrono::TimeZone;
    use chrono::Utc;
    use surrealdb::sql::Thing;

    use crate::db;
    use crate::db::FUHLC;
    use crate::model;
    use crate::model::kline::Klt;
    use crate::model::trade::trade_storder::TradeStorder;
    use crate::model::trade::trade_strategy::TradeStrategy;
    use crate::model::FudaRecord;

    #[tokio::test]
    async fn test_upsert_trade_storder() {
        let _ = db::init().await.unwrap();
    }

    #[tokio::test]
    async fn test_select_trade_storders() {
        // generate a timestamp
        let ts = FUHLC.new_timestamp();
        println!("\nexercise ts = {}", ts.to_string());

        let _ = db::init().await.unwrap();

        let result = db::trade::trade_storder::select(None, None, None, 0, 10).await;

        println!("result= {:?}\n", result);
        assert_eq!(result.is_ok(), true);

        if let Ok(values) = &result {
            for (index, value) in values.iter().enumerate() {
                println!("\n[{:02}]={:?}", index, value);
            }
        }
    }

    #[tokio::test]
    async fn test_delete_trade_storders_by_trade_bot_id() {
        let _ = db::init().await.unwrap();

        let result = db::trade::trade_storder::delete_all_by("j9tpv2dyh3op2jwgr107").await;

        println!("result= {:?}\n", result);
    }
}
